# -*- coding:utf8 -*-

import MySQLdb
from gaokaopai import settings
import time, random
from gaokaopai import util
import datetime

mysql_conn = MySQLdb.connect(host = settings.MYSQL_SERVER,
                             user = settings.MYSQL_USER,
                             passwd = settings.MYSQL_PASS,
                             db = settings.MYSQL_DB, charset='utf8')
mysql_cursor = mysql_conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
mysql_conn.autocommit(True)


def getListProvince():
    sql = "SELECT * FROM  `t_city` WHERE pid=0 AND gid IS NOT NULL ORDER BY `id` ASC "
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchall()

def getProvince(name):
    sql = "select * from t_city where pid=0 and name='%s'" % name
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchone()

def getRunProvince():
    sql = "select * from t_city where pid=0 AND gid IS NOT NULL and progress=1 order by id asc limit 1"
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchone()

def updateRunProvince(id):
    sql = "UPDATE t_city SET progress=2 WHERE id=%s" % id
    mysql_cursor.execute(sql)

def updateUniversityStude(fid, student):
    sql = "UPDATE t_university SET student='%s' WHERE fid='%s'" % (student, fid)
    mysql_cursor.execute(sql)

'''
结巴分词
'''
def getListUniversity(province):
    sql = "select id, province, city, cityinfo from t_university where cityinfo like '"+province+"%' and province =0"
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchall()

def getCity(cityname):
    sql = "select * from t_city where name like '"+cityname+"%'"
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchone()

def updateUniversity(id, province, city):
    sql = "update t_university set province=%s, city=%s where id=%s" % (province, city, id)
    mysql_cursor.execute(sql)

def updateUniversityName(id, name):
    sql = "update t_university set name='%s' where id=%s" % (name, id)
    mysql_cursor.execute(sql)

'''民办'''
def updateMinUniversity(fid, isgov):
    sql = "update t_university set isgov=%s where fid='%s'" % (isgov, fid)
    print(sql)
    mysql_cursor.execute(sql)

'''大专'''
def updateDaUniversity(fid, ismajor):
    sql = "update t_university set ismajor=%s where fid='%s'" % (ismajor, fid)
    mysql_cursor.execute(sql)

def getUniversity(name):
    sql = "select * from t_university where name = '"+name+"'"
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchone()

def getUniversityByFid(fid):
    sql = "select * from t_university where fid = '"+str(fid)+"'"
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchone()

def getListBKUniversity():
    sql = "select * from t_bk_university where status=1 order by id asc limit 10"
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchall()

def updateBKUniversity(id, status):
    sql = "update t_bk_university set status=%s where id='%s'" % (status, id)
    mysql_cursor.execute(sql)

def getListMajor(limit=10):
    sql = "select * from t_major where type=2 and level=3 and crawl=1 order by id asc limit %s" % limit
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchall()

def getMajor(code):
    sql = "select * from t_major where code='%s' limit 1" % code
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchone()

def getMajorByNameAndCode(code, name, type):
    sql = "select * from t_major where code='%s' and name='%s' and type='%s' limit 1" % (code, name, type)
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchone()


def getUniversityMajor(name, fid, type=2):
    sql = "select * from t_university_major where name='%s' and fid='%s' and type='%s'" % (name, fid, type)
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchone()

def updateUniversityMajor(id, rank, rankname):
    sql = "update t_university_major set rank=%s, rankname='%s' where id='%s'" % (rank, rankname, id)
    mysql_cursor.execute(sql)


def getMajorRank(name, fid, type=2):
    sql = "select * from t_major_rank where name='%s' and fid='%s' and type='%s'" % (name, fid, type)
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchone()

def insertMajorRank(fid, name, type, rank, rankname, matching):
    now_date = datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d %H:%M:%S')
    sql = "insert into t_major_rank (fid, name, type, rank, rankname, matching, created_at, updated_at) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % \
          (fid, name, type, rank, rankname, matching, now_date, now_date)
    mysql_cursor.execute(sql)

def getUniversityHistory(fid, year):
    sql = "select * from t_university_history where fid='%s' and year='%s'" % (fid, year)
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchone()

def getUniversityPoint(fid, special, name, isself=0):
    sql = "select * from t_university_point where fid='%s' and special='%s' and name='%s' and isself='%s'" % (fid, special, name, isself)
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchone()

def getUniversityCollege(fid, name, pid=0, level=1, type=0, isself=0):
    if type > 0:
        sql = "select * from t_university_college where fid='%s' and name='%s' and level=%s and pid=%s and `type`='%s' and isself='%s'" % (fid, name, level, pid, type, isself)
    else:
        sql = "select * from t_university_college where fid='%s' and name='%s' and level=%s and pid=%s" % (fid, name, level, pid)
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchone()

def insertUniversityCollegeLevelOne(fid, name):
    now_date = datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d %H:%M:%S')
    sql = "insert into t_university_college (fid, name, level, created_at, updated_at) values ('%s', '%s', '%s', '%s', '%s')" % \
          (fid, name, 1, now_date, now_date)
    mysql_cursor.execute(sql)

    return int(mysql_cursor.lastrowid)

def getMiddleSchool(province, name):
    sql = "select * from t_middle_school where name='%s' and province='%s'" % (name, province)
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchone()

def executeSql(sql):
    mysql_cursor.execute(sql)
def insert(name, url):
    sql = "select id from scrapy_university where name='%s'" % name

    mysql_cursor.execute(sql)
    ret = mysql_cursor.fetchone()
    if ret == None:
        sql = "insert into scrapy_university (name, url) values( '%s', '%s')" % (name, url)
        mysql_cursor.execute(sql)

def selectScrapyUniversity():
    sql = "select * from scrapy_university where id between 31 and 50"
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchall()
def selectScrapyProvince():
    sql = "select * from scrapy_province "
    mysql_cursor.execute(sql)
    return mysql_cursor.fetchall()